SQL: Setting local date and time for a session in Azure SQL Database

Yesterday, I posted about how to get local date and time when you’re working with Azure SQL Database. It uses UTC and there’s no option (at least not currently) to set a timezone for a database.

I mentioned that to get the local date and time, you could execute code like this:

CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'AUS Eastern Standard Time' AS datetime)

Now what many people haven’t noticed is that the time zone name can actually be a variable. (That’s refreshing because so many times in SQL Server, these things require constants). So this also works:

DECLARE @UserTimeZone sysname = N'AUS Eastern Standard Time';

SELECT CAST(SYSDATETIMEOFFSET() AT TIME ZONE @UserTimeZone AS datetime);

Note: sysname is the standard data type for system objects and it’s the datatype that’s used for these time zones. I found that out by looking at the datatypes returned by querying the sys.time_zone_info view. sysname is currently mapped to nvarchar(128).

Now this means that I could also make the timezone be a value that I retrieve from the database.

Session or Database Level

The pain with that option is that I’d need to retrieve it in every piece of code that needs the current date and/or time. But there is another way around that.

We can store values at the session level, that are present for the entire session. So in my application, I could execute the following code every time that someone logs on:

EXEC sp_set_session_context N'UserTimeZone', N'AUS Eastern Standard Time';

Then later we can just use that value whenever we need the current date and time:

SELECT CAST(SYSDATETIMEOFFSET() AT TIME ZONE
            CAST(SESSION_CONTEXT(N'UserTimeZone') AS sysname) AS datetime);

And of course if you just want the date, change the final cast to a date, not a datetime.

It’s a lot wordier than GETDATE() or SYSDATETIME() but at least it works.

2020-03-13